package com.kaikeba.estack.common.jdbc;

import com.alibaba.druid.util.StringUtils;
import com.kaikeba.estack.exception.DataAccessException;
import com.kaikeba.estack.web.ApplicationContext;

import javax.servlet.http.HttpServletRequest;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import java.util.stream.Collectors;

/**
 * 操作jdbc相关操作的工具类
 * 支持insert update delete query
 *
 * @Auth xingwan
 * @Date 2020/9/6 22:26
 **/
public class JdbcUtil {

    /**
     * 匹配sql中的:变量语法的正则表达式如：
     * select * from demo where id = :id
     * 可以匹配出来:id
     */
    private static final Pattern PARAM_NAME_PATTERN = Pattern.compile("(:([\\w|\\_]+)?)");


    public static void execute(Connection connection, String sql, Object... parameters) throws SQLException {
        execute(connection, sql, Arrays.asList(parameters));
    }


    public static void execute(Connection conn, String sql) throws SQLException {
        execute(conn, sql, Collections.EMPTY_LIST);
    }

    public static void execute(Connection conn, String sql, List<Object> parameters) throws SQLException {
        PreparedStatement stmt = null;

        try {
            stmt = conn.prepareStatement(sql);

            setParameters(stmt, parameters);

            stmt.executeUpdate();
        } finally {
            DataSourceUtil.close(stmt);
            DataSourceUtil.close(conn);
        }
    }

    /**
     * 更新数据
     *
     * @param conn
     * @param sql
     * @param parameters
     * @return
     * @throws SQLException
     */
    public static int execute(Connection conn, String sql, Map<String, Object> parameters) throws SQLException {
        PreparedStatement stmt = null;
        int updateCount;
        try {
            stmt = handlerNamedSqlAndSetParams(conn, sql, parameters);
            updateCount = stmt.executeUpdate();
        } finally {
            DataSourceUtil.close(stmt);
            DataSourceUtil.close(conn);
        }

        return updateCount;
    }

    /**
     * 查询数据
     *
     * @param conn
     * @param sql
     * @return
     * @throws SQLException
     */
    public static List<Map<String, Object>> queryForListMap(Connection conn, String sql)
            throws SQLException {
        return queryForListMap(conn, sql, Collections.EMPTY_LIST, MapRowMapper.getInstance());
    }

    /**
     * 查询数据
     *
     * @param conn
     * @param sql
     * @param parameters
     * @return
     * @throws SQLException
     */
    public static List<Map<String, Object>> queryForListMap(Connection conn, String sql, Map<String, Object> parameters)
            throws SQLException {
        List<Map<String, Object>> rows = new ArrayList<>();
        PreparedStatement stmt = null;
        ResultSet rs = null;
        try {
            stmt = handlerNamedSqlAndSetParams(conn, sql, parameters);
            rs = stmt.executeQuery();
            long rowNum = 1;
            while (rs.next()) {
                rows.add(MapRowMapper.getInstance().mapRow(rs, rowNum++));
            }
        } finally {
            DataSourceUtil.close(rs);
            DataSourceUtil.close(stmt);
            DataSourceUtil.close(conn);
        }

        return rows;
    }

    /**
     * 查询数据
     *
     * @param conn
     * @param sql
     * @param parameters
     * @return
     * @throws SQLException
     */
    public static List<Map<String, Object>> queryForListMap(Connection conn, String sql, List<Object> parameters)
            throws SQLException {
        return queryForListMap(conn, sql, parameters, MapRowMapper.getInstance());
    }

    /**
     * 泛型返回bean类型的数据
     *
     * @param conn
     * @param sql
     * @param parameters
     * @return
     * @throws SQLException
     */
    public static List<Map<String, Object>> queryForListMap(Connection conn, String sql, Object... parameters)
            throws SQLException {
        List<Map<String, Object>> rows = new ArrayList<>();
        PreparedStatement stmt = null;
        ResultSet rs = null;
        try {
            stmt = conn.prepareStatement(sql);
            setParameters(stmt, Arrays.asList(parameters));
            rs = stmt.executeQuery();
            long rowNum = 1;
            while (rs.next()) {
                rows.add(MapRowMapper.getInstance().mapRow(rs, rowNum++));
            }
        } finally {
            DataSourceUtil.close(rs);
            DataSourceUtil.close(stmt);
            DataSourceUtil.close(conn);
        }

        return rows;
    }

    /**
     * 泛型返回一条数据
     *
     * @param conn
     * @param sql
     * @return
     * @throws SQLException
     */
    public static <T> T queryForObject(Connection conn, String sql, Class<T> tClass)
            throws SQLException {
        return queryForObject(conn, sql, (List<Object>) Collections.EMPTY_LIST, tClass);
    }

    /**
     * 泛型返回一条数据
     *
     * @param conn
     * @param sql
     * @param parameters
     * @return
     * @throws SQLException
     */
    public static <T> T queryForObject(Connection conn, String sql, List<Object> parameters, Class<T> tClass)
            throws SQLException {
        T row = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        try {
            stmt = conn.prepareStatement(sql);
            setParameters(stmt, parameters);
            rs = stmt.executeQuery();
            long rowNum = 1;
            while (rs.next()) {
                if (rowNum > 1) {
                    throw new DataAccessException("返回的结果集不只一条");
                }
                row = SingleColumnRowMapper.getInstance(tClass).mapRow(rs, rowNum++);
            }
        } finally {
            DataSourceUtil.close(rs);
            DataSourceUtil.close(stmt);
            DataSourceUtil.close(conn);
        }

        return row;
    }

    /**
     * 泛型返回一条数据
     *
     * @param conn
     * @param sql
     * @param parameters
     * @return
     * @throws SQLException
     */
    public static <T> T queryForObject(Connection conn, String sql, Class<T> tClass, Object... parameters)
            throws SQLException {
        T row = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        try {
            stmt = conn.prepareStatement(sql);
            setParameters(stmt, Arrays.asList(parameters));
            rs = stmt.executeQuery();
            long rowNum = 1;
            if (rs.next()) {
                row = SingleColumnRowMapper.getInstance(tClass).mapRow(rs, rowNum++);
            }
        } finally {
            DataSourceUtil.close(rs);
            DataSourceUtil.close(stmt);
            DataSourceUtil.close(conn);
        }

        return row;
    }


    /**
     * 泛型返回一条数据
     *
     * @param conn
     * @param sql
     * @param parameters
     * @return
     * @throws SQLException
     */
    public static <T> T queryForObject(Connection conn, String sql, Class<T> tClass, Map<String, Object> parameters)
            throws SQLException {
        T row = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        try {
            stmt = handlerNamedSqlAndSetParams(conn, sql, parameters);
            rs = stmt.executeQuery();
            long rowNum = 1;
            if (rs.next()) {
                row = SingleColumnRowMapper.getInstance(tClass).mapRow(rs, rowNum++);
            }
        } finally {
            DataSourceUtil.close(rs);
            DataSourceUtil.close(stmt);
            DataSourceUtil.close(conn);
        }

        return row;
    }

    /**
     * 泛型返回bean类型的数据
     *
     * @param conn
     * @param sql
     * @param tClass
     * @param <T>
     * @return
     * @throws SQLException
     */
    public static <T> List<T> queryForList(Connection conn, String sql, Class<T> tClass)
            throws SQLException {
        return queryForList(conn, sql, null, tClass);
    }

    /**
     * 泛型返回bean类型的数据
     *
     * @param conn
     * @param sql
     * @param parameters
     * @param tClass
     * @param <T>
     * @return
     * @throws SQLException
     */
    public static <T> List<T> queryForList(Connection conn, String sql, Class<T> tClass, Map<String, Object> parameters)
            throws SQLException {
        List<T> rows = new ArrayList<>();
        PreparedStatement stmt = null;
        ResultSet rs = null;
        try {
            stmt = handlerNamedSqlAndSetParams(conn, sql, parameters);
            rs = stmt.executeQuery();
            long rowNum = 1;
            while (rs.next()) {
                rows.add(BeanRowMapper.getInstance(tClass).mapRow(rs, rowNum++));
            }
        } finally {
            DataSourceUtil.close(rs);
            DataSourceUtil.close(stmt);
            DataSourceUtil.close(conn);
        }
        return rows;
    }

    /**
     * 泛型返回bean类型的数据
     *
     * @param conn
     * @param sql
     * @param parameters
     * @param tClass
     * @param <T>
     * @return
     * @throws SQLException
     */
    public static <T> List<T> queryForList(Connection conn, String sql, Class<T> tClass, Object... parameters)
            throws SQLException {
        return queryForList(conn, sql, Arrays.asList(parameters), tClass);
    }

    /**
     * 泛型返回bean类型的数据
     *
     * @param conn
     * @param sql
     * @param parameters
     * @param tClass
     * @param <T>
     * @return
     * @throws SQLException
     */
    public static <T> List<T> queryForList(Connection conn, String sql, List<Object> parameters, Class<T> tClass)
            throws SQLException {
        List<T> rows = new ArrayList<>();
        PreparedStatement stmt = null;
        ResultSet rs = null;
        try {
            stmt = conn.prepareStatement(sql);
            setParameters(stmt, parameters);
            rs = stmt.executeQuery();
            long rowNum = 1;
            BeanRowMapper<T> rowMapper = BeanRowMapper.getInstance(tClass);
            while (rs.next()) {
                rows.add(rowMapper.mapRow(rs, rowNum++));
            }
        } finally {
            DataSourceUtil.close(rs);
            DataSourceUtil.close(stmt);
        }

        return rows;
    }

    /**
     * 泛型返回bean类型的数据
     *
     * @param conn
     * @param sql
     * @param parameters
     * @param rowMapper
     * @param <T>
     * @return
     * @throws SQLException
     */
    public static <T> List<T> queryForList(Connection conn, String sql, List<Object> parameters, RowMapper<T> rowMapper)
            throws SQLException {
        List<T> rows = new ArrayList<>();
        PreparedStatement stmt = null;
        ResultSet rs = null;
        try {
            stmt = conn.prepareStatement(sql);
            setParameters(stmt, parameters);
            rs = stmt.executeQuery();
            long rowNum = 1;
            while (rs.next()) {
                rows.add(rowMapper.mapRow(rs, rowNum++));
            }
        } finally {
            DataSourceUtil.close(rs);
            DataSourceUtil.close(stmt);
        }

        return rows;
    }

    private static void setParameters(PreparedStatement stmt, List<Object> parameters) throws SQLException {
        if (parameters == null) {
            return;
        }
        for (int i = 0, size = parameters.size(); i < size; ++i) {
            Object param = parameters.get(i);
            stmt.setObject(i + 1, param);

        }
    }

    private static PreparedStatement handlerNamedSqlAndSetParams(Connection stmt, String sql, Map<String, Object> parameters) throws SQLException {
        Matcher matcher = PARAM_NAME_PATTERN.matcher(sql);
        List<Object> params = new ArrayList<>();
        StringBuffer prepareSql = new StringBuffer();
        while (matcher.find()) {
            String paramName = matcher.group(2);
            if (StringUtils.isEmpty(paramName)) {
                continue;
            }
            Object paramValue = parameters.get(paramName);
            //集合类型需要单独处理 说明有in存在
            if (paramValue instanceof Collection) {
                Collection value = (Collection) paramValue;
                params.addAll(value);
                String inParams = (String) value.stream().map(k -> "?").collect(Collectors.joining(","));
                //将匹配的到分组替换为指定字符串之后，追加到StringBuffer中
                matcher.appendReplacement(prepareSql, inParams);
            } else {
                params.add(paramValue);
                matcher.appendReplacement(prepareSql, "?");
            }
        }
        //将匹配剩下的sql追加到StringBuffer中
        matcher.appendTail(prepareSql);
        PreparedStatement preparedStatement = stmt.prepareStatement(prepareSql.toString());
        setParameters(preparedStatement, params);
        return preparedStatement;
    }
}
